package com.example.project.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface StatisticsMapper {

	@Select("SELECT p.name AS projectName, p.customer_name AS customerName"
			+ "CASE p.state WHEN 1 THEN '运营中' ELSE '已停运' END AS state, "
			+ "COUNT(DISTINCT i.id) AS '总需求数'" + "SUM(CASE WHEN i.status_id IN (4, 6) THEN 1 ELSE 0 END) AS '已关闭需求数', "
			+ "SUM(CASE WHEN i.status_id NOT IN (4, 6) THEN 1 ELSE 0 END) AS '未关闭需求数', "
			+ "ROUND(SUM(CASE WHEN i.status_id IN (4, 6) THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(DISTINCT i.id), 0), 2) AS '需求完成率(%)', "
			+ "ROUND(AVG(CASE WHEN i.status_id IN (4, 6) THEN TIMESTAMPDIFF(HOUR, i.create_time, i.update_time) ELSE NULL END), 2) AS '平均处理时效(小时)' "
			+ "FROM t_project p LEFT JOIN t_issue i ON p.id = i.project_id AND i.del_flag = 0 "
			+ "WHERE p.del_flag = 0 AND i.create_time BETWEEN #{startDate} AND ${endDate} "
			+ "GROUP BY p.id, p.name, p.customer_name, p.state ")
	public List<Map<String, Object>> queryProjectForIssueDetail(String startDate, String endDate);

}
